﻿using System;
using System.Collections.Generic;
using System.Data.OracleClient;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Tilver.Data.DataPager
{
    /// <summary>
    /// sql语句操作类
    /// </summary>
    internal class SqlParser
    {
        public SqlStruct Parse(string sql)
        {
            sql = Regex.Replace(sql, "[\r\n]", " ", RegexOptions.IgnoreCase);
            var result = new SqlStruct();
            result.Select = GetFields(sql);
            result.From = GetTables(sql);
            result.Where = GetWhere(sql);
            result.GroupBy = GetGroup(sql);
            result.Having = GetHaving(sql);
            result.OrderBy = GetOrderBy(sql);
            return result;
        }

        private string GetFields(string sql)
        {
            var regex = "(select)(.+)(from)";
            return GetMatchedString(regex, sql);
        }

        private string GetTables(string sql)
        {
            var regex = string.Empty;
            if (IsContains(sql, "where\\s+"))
            {
                regex = "(from)(.+)(where\\s+)";
            }
            else if (IsContains(sql, "group\\s+by"))
            {
                regex = "(from)(.+)(group\\s+by)";
            }
            else if (IsContains(sql, "having\\s"))
            {
                regex = "(from)(.+)(having\\s)";
            }
            else if (IsContains(sql, "order\\s+by"))
            {
                regex = "(from)(.+)(order\\s+by)";
            }
            else
            {
                regex = "(from)(.+)($)";
            }
            return GetMatchedString(regex, sql);
        }

        private string GetWhere(string sql)
        {
            var regex = "";
            if (IsContains(sql, "\\s+where\\s+"))
            {
                if (IsContains(sql, "group\\s+by"))
                {
                    regex = "(where)(.+)(group\\s+by)";
                }
                else if (IsContains(sql, "having\\s"))
                {
                    regex = "(where)(.+)(having\\s)";
                }
                else if (IsContains(sql, "order\\s+by"))
                {
                    regex = "(where)(.+)(order\\s+by)";
                }
                else
                {
                    regex = "(where)(.+)($)";
                }
            }
            else
            {
                return string.Empty;
            }
            return GetMatchedString(regex, sql);
        }


        private string GetGroup(string sql)
        {
            var regex = string.Empty;
            if (IsContains(sql, "group\\s+by"))
            {
                if (IsContains(sql, "having\\s"))
                {
                    regex = "(group\\s+by)(.+)(having\\s)";
                }
                else if (IsContains(sql, "order\\s+by"))
                {
                    regex = "(group\\s+by)(.+)(order\\s+by)";
                }
                else
                {

                    regex = "(group\\s+by)(.+)($)";
                }
            }
            else
            {
                return string.Empty;
            }
            return GetMatchedString(regex, sql);
        }

        private string GetHaving(string sql)
        {
            var regex = string.Empty;
            if (IsContains(sql, "having\\s"))
            {
                if (IsContains(sql, "(order\\s+by)"))
                {
                    regex = "(having\\s)(.+)((order\\s+by))";
                }
                else
                {
                    regex = "(having\\s)(.+)($)";
                }
            }
            else
                return string.Empty;
            return GetMatchedString(regex, sql);
        }

        private string GetOrderBy(string sql)
        {
            var regex = string.Empty;
            if (IsContains(sql, "order\\s+by"))
            {
                regex = "(order\\s+by)(.+)($)";
            }
            else
            {
                return string.Empty;
            }
            return GetMatchedString(regex, sql);
        }

        private static String GetMatchedString(String pattern, String text)
        {
            var regex = new Regex(pattern, RegexOptions.IgnoreCase);
            var matcher = regex.Match(text);
            while (matcher.Success)
            {
                return matcher.Groups[2].Value.Trim();
            }
            return null;
        }

        private static bool IsContains(String lineText, String word)
        {
            var regex = new Regex(word, RegexOptions.IgnoreCase);
            var match = regex.Match(lineText);
            return match.Success;
        }
    }

    /// <summary>
    /// sql语句结构类
    /// </summary>
    internal class SqlStruct
    {
        /// <summary>
        /// SELECT
        /// </summary>
        public string Select { get; set; }
        /// <summary>
        /// FROM
        /// </summary>
        public string From { get; set; }
        /// <summary>
        /// WHERE
        /// </summary>
        public string Where { get; set; }
        /// <summary>
        /// GROUP BY
        /// </summary>
        public string GroupBy { get; set; }
        /// <summary>
        /// HAVING
        /// </summary>
        public string Having { get; set; }
        /// <summary>
        /// ORDER BY
        /// </summary>
        public string OrderBy { get; set; }

        public override string ToString()
        {
            var sb = new StringBuilder();
            sb.Append("SELECT ");
            if (!string.IsNullOrEmpty(Select))
                sb.Append(Select);
            else
                sb.Append("*");
            sb.AppendLine();
            sb.AppendFormat(" FROM {0} ", From);
            sb.AppendLine();
            sb.AppendFormat(" WHERE {0} ", Where);
            sb.AppendLine();
            sb.AppendFormat(" GROUP BY {0} ", GroupBy);
            sb.AppendLine();
            sb.AppendFormat(" HAVING {0} ", Having);
            sb.AppendLine();
            sb.AppendFormat(" ORDER BY {0} ", OrderBy);
            sb.AppendLine();
            return sb.ToString();
        }
    }
    /// <summary>
    /// 分页sql语句类
    /// </summary>
    internal class SqlPagerContext
    {

        /// <summary>
        /// 分页大小
        /// </summary>
        public long PageSize { get; set; }
        ///// <summary>
        ///// 页码
        ///// </summary>
        //public long PageIndex { get; set; }
        /// <summary>
        /// 分页sql语句
        /// </summary>
        public string PagerSql { get; set; }
        /// <summary>
        /// 获取首页数据的sql语句
        /// </summary>
        public string FirstPageSql { get; set; }
        /// <summary>
        /// 计算记录数的sql语句
        /// </summary>
        public string RecordCountSql { get; set; }

        ///// <summary>
        ///// 查询Sql语句
        ///// </summary>
        //public string QuerySql { get; set; }
        ///// <summary>
        ///// sql查询结构
        ///// </summary>
        //public SqlStruct SqlStruct { get; set; }
        ///// <summary>
        ///// 查询关键字段
        ///// </summary>
        //public string KeyField { get; set; }
    }

    #region 公用方法
    internal class SqlPagerUtil
    {
        #region 私有方法

        public static string GetFieldsAlias(string fields)
        {
            string ret = string.Empty;
            if (fields != "*")
            {
                string[] fs = fields.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string s in fs)
                {
                    string tmp = s.Trim();
                    if (tmp.Contains(" "))
                    {
                        ret += tmp.Substring(tmp.LastIndexOf(" ") + 1) + ", ";
                    }
                    else ret += tmp + ", ";
                }
                ret = fields.TrimEnd(',', ' ');
            }
            else ret = fields;
            return ret;
        }
        /// <summary>
        /// 获取表主键字段(SqlServer)
        /// </summary>
        /// <param name="database"></param>
        /// <param name="from"></param>
        /// <returns></returns>
        public static string GetKeyField(DbDataBase database, string from)
        {
            string ret = string.Empty;
            try
            {
                object result = new DbAdoBase(@"SELECT t1.name FROM sys.columns t1, sys.index_columns t2
			                        , (SELECT b.object_id, b.index_id FROM sys.indexes a, sys.index_columns b
				                        WHERE a.object_id = b.object_id AND a.index_id = b.index_id AND a.is_primary_key = 1 
					                        AND a.object_id = OBJECT_ID(@From)
				                        GROUP BY b.object_id, b.index_id
				                        HAVING count(*) = 1) t3
		                        WHERE t1.object_id = t2.object_id AND t2.object_id = t3.object_id
			                        AND t1.column_id=t2.column_id AND t2.index_id = t3.index_id", database)
                    // .AddParameter(new SqlParameter("@From", from))
                    .AddInParameter("@From", from)
                     .ExecuteScalar();
                //获取只有一个字段的主键
                ret = (result == null) ? string.Empty : result.ToString().Trim();
            }
            catch { }
            return ret;
        }
        #endregion
    }
    #endregion
}
